package cn.fintecher.pangolin.service.dataimp.repository;

import cn.fintecher.pangolin.common.enums.UserStatus;
import cn.fintecher.pangolin.entity.mysql.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @Auther: xiaqun
 * @Description:
 * @Date: 11:32 2019/9/23
 */
public interface UserRepository extends JpaRepository<User, String>, QuerydslPredicateExecutor<User> {
    /**
     * 导出用户字段
     */
    @Query(value = "SELECT a.username,a.real_name,a.station," +
            "case a.is_manage when 'YES' then '是' else '否' end," +
            "case a.`status` when 'WORKING' then '在职' when 'LEAVE' then '离职' else '锁定' end," +
            "c.`name`,b.certificate_no," +
            "case b.sex when 'MALE' then '男' when 'FEMALE' then '女' else '未知' end," +
            "b.age,b.birthplace,b.nation,b.political_status," +
            "case b.marital_status when 'UNMARRIED' then '未婚' when 'MARRIED' then '已婚' else '未知' end," +
            "b.education,b.profession,b.school,DATE_FORMAT(b.graduation_time,'%Y-%m-%d'),DATE_FORMAT(b.join_work_time,'%Y-%m-%d'),b.phone_no,b.mobile_no,b.email,b.experience,b.last_employer," +
            "b.emergency_contact_name,b.emergency_contact_no,b.home_address,b.residence_address,DATE_FORMAT(b.entry_time,'%Y-%m-%d'),b.work_age,b.insurance,b.bank,b.bank_card," +
            "DATE_FORMAT(b.first_sign_date,'%Y-%m-%d'),DATE_FORMAT(b.second_sign_date,'%Y-%m-%d'),DATE_FORMAT(b.third_sign_date,'%Y-%m-%d'),DATE_FORMAT(b.first_expire_date,'%Y-%m-%d')," +
            "DATE_FORMAT(b.second_expire_date,'%Y-%m-%d'),DATE_FORMAT(b.leave_date,'%Y-%m-%d'),b.leave_reason,b.remark " +
            "from `user` a left join user_detail b on a.user_detail_id = b.id left join department c on a.depart_id = c.id where a.id in (:ids)", nativeQuery = true)
    List<Object[]> exportUser(@Param("ids") List<String> ids);

    /**
     * 导出用户字段
     */
    @Query(value = "SELECT a.username,a.real_name,a.station," +
            "case a.is_manage when 'YES' then '是' else '否' end," +
            "case a.`status` when 'WORKING' then '在职' when 'LEAVE' then '离职' else '锁定' end," +
            "c.`name`,b.certificate_no," +
            "case b.sex when 'MALE' then '男' when 'FEMALE' then '女' else '未知' end," +
            "b.age,b.birthplace,b.nation,b.political_status," +
            "case b.marital_status when 'UNMARRIED' then '未婚' when 'MARRIED' then '已婚' else '未知' end," +
            "b.education,b.profession,b.school,DATE_FORMAT(b.graduation_time,'%Y-%m-%d'),DATE_FORMAT(b.join_work_time,'%Y-%m-%d'),b.phone_no,b.mobile_no,b.email,b.experience,b.last_employer," +
            "b.emergency_contact_name,b.emergency_contact_no,b.home_address,b.residence_address,DATE_FORMAT(b.entry_time,'%Y-%m-%d'),b.work_age,b.insurance,b.bank,b.bank_card," +
            "DATE_FORMAT(b.first_sign_date,'%Y-%m-%d'),DATE_FORMAT(b.second_sign_date,'%Y-%m-%d'),DATE_FORMAT(b.third_sign_date,'%Y-%m-%d'),DATE_FORMAT(b.first_expire_date,'%Y-%m-%d')," +
            "DATE_FORMAT(b.second_expire_date,'%Y-%m-%d'),DATE_FORMAT(b.leave_date,'%Y-%m-%d'),b.leave_reason,b.remark " +
            "from `user` a left join user_detail b on a.user_detail_id = b.id left join department c on a.depart_id = c.id", nativeQuery = true)
    List<Object[]> exportUser();

    /**
     * 根据用户名查询
     */
    User findByUsername(String username);

    /**
     * 获取用户集合
     */
    List<User> findAllByStatus(List<UserStatus> status);
}
